Excel

ELEARNING - FREIBURG

Excel - Funktionen

10 - SVerweis - Teil 2

Thema: Suchen mit der Funnktion SVerweis()

Wir haben die Suchfunktion SVerweis() bereits mit dem Suchmodus "genaue Übereinstimmung" kennengelernt.

Hier noch einmal die Syntax:
=SVerweis(Suchkriterium;Suchtabelle;SpaltenNr;Suchmodus)

Welchen Sinn sollte eine Suche im Modus "ungefähre Übereinstimmung" haben? Diese Frage wird durch das folgende Beispiel (Arbeitsblatt "Frachtkosten 1") geklärt.

Hier haben wir eine Frachttabelle, in der gestaffelte Preise vorliegen. Ab 0 km Fahrweg werden 2,80 € an Gebühren fällig, ab 5 km werden 3,20 € fällig usw.

In der Zelle C13 soll nun die gefahrene Strecke eingegeben werden können und in der Zelle C14 sollen die jeweiligen Gebühren ermittelt werden. Es kann natürlich vorkommen, dass die eingegebene Kilometerzahl, beispielsweise 11, nicht in der Frachttabelle direkt vorkommt. In diesem Fall soll der Suchbefehl auf den nächstkleineren Wert zurückfallen und den hierfür geltenden Preis liefern. Genau dies ist das Verhalten, wenn wir die Funktion SVerweis() im Suchmodus "ungefähre Übereinstimmung" verwenden.

HINWEIS:
Damit die Suche das korrekte Ergebnis liefert, muss sichergestellt sein, dass die Frachttabelle nach km aufsteigend sortiert ist.

Die Formel für die Zelle C14 in unserem Beispiel lautet:

=SVerweis(C13;A5:B10;2;WAHR)

So wird beispielsweise bei der Eingabe von 11 (in C13) der korrekte Betrag von 3,80 € (in C14) ermittelt. Auch bei einer Eingabe von 109 (in C13) fällt die Suchfunktion SVerweis() auf den nächstkleineren Wert zurück und findet dort den korrekten Betrag von 6,50 €.

Im Arbeitsblatt "Frachtkosten 2" haben wir das Beispiel noch etwas ausgebaut.

Dort sehen Sie die Grundpreise für die gefahrenen Kilometer von drei verschiedenen Speditionen. Neben der Kilometerzahl in C12 soll nun außerdem die Spedition durch Eingabe der Spaltennummer in C13 gewählt werden können. Die Formel in C14 lautet dann:

=SVerweis(C12;A5:D10;C13;WAHR)

Sie sehen, dass wir diesmal die gesamte Frachttabelle, also den Bereich A5:D10 als Suchbereich verwenden und wir die Nummer der Ergebnisspalte aus der Zelle C13 beziehen.

Die Eingabe einer Spaltennummer in C13 ist für den Benutzer zwar nicht besonders komfortabel, aber immerhin findet der Suchbefehl die korrekten Grundpreise.

Im Arbeitsblatt "Frachtkosten 3" haben wir das Beispiel erneut etwas ausgebaut.

Neben der ursprünglichen km-Tabelle gibt es noch eine weitere Tabelle, in der die Gebühren für die verschiedenen Päckchengewichte festgelegt sind. Für ein Päckchen mit einem Gewicht von 0,6 kg wird beispielsweise bei der Spedition "Auf und davon" ein Aufpreis von 0,70 € berechnet. In der Abbildung oben sehen Sie, dass im blau hinterlegten Bereich die gefahrene Strecke, das Gewicht des Päckchens in Kilogramm und die Spaltennummer für die jeweilige Spedition eingegeben werden sollen, so dass in den Zellen D16 und D17 jeweils der Grundpreis und der Aufpreis und in C18 die Gesamtkosten ermittelt werden.

Für Zelle D16 lautet die Formel: =SVerweis(D13,A5:D10;D15;WAHR)

Für Zelle D17 lautet die Formel: =SVerweis(D14,F5:I10;D15;WAHR)

Für Zelle D18 lautet die Formel: =D16+D17

Nach wie vor ist es für den Benutzer unbefriedigend, dass er in der Zelle D15 statt dem Namen der Spedition deren Spaltennummer eingeben muss! Wie Sie hierfür eine benutzerfreundliche Lösung bereitstellen, erfahren Sie im Video.